from datetime import datetime, timedelta

from flask import Blueprint, request, current_app, send_file
from openpyxl import load_workbook
import pandas
import pymysql, traceback

from utilsapp.common import parsing_data_id, assign_value , get_collection_name

import utilsapp.excel_util
from utilsapp import utils
import time, os, uuid
from utilsapp.mysql_con import (
    db_connect,
    db_delete,
    db_deletes,
    db_insert,
    db_list,
    db_table_list,
    db_fetchone,
    db_update,
    get_sensor_new_table,
)
from utilsapp.mysql_con import db_operation, db_pagelist
from utilsapp.common import parsing_permissions
from pathlib import Path
sensor = Blueprint("sensor", __name__)


# 查询传感器列表
@sensor.route("/getlist")
def get_sensor_list():
    flag = parsing_permissions(request)  # 获取用户权限
    size = int(request.args.get("size"))
    page = int(request.args.get("page"))
    data_id = flag["dataId"]  # 数据权限 dataID
    if data_id != 0:  # 不是超级管理员
        type_ = flag["type"]
        sql = f"""select s.id,s.sensor_name,s.address,s.port,s.eigenvalue,s.direction,d.device_name,g.name as group_name ,
        c.name as company_name,f.name as factory_name,z.name as zone_name from sensor s,device d,regionlevel g,
        regionlevel c,regionlevel f,regionlevel z where s.{type_}_id={data_id} and d.id=s.device_id and g.id=s.group_id
         and c.id=s.company_id and f.id = s.factory_id and z.id=s.zone_id 
        LIMIT {max(0, (page - 1) * size)},{size}"""
        sql_1 = f"select count(*) as num from sensor where {type_}_id={data_id}"

    else:
        sql = f"""select s.id,s.sensor_name,d.device_name,g.name as group_name ,c.name as company_name,f.name as factory_name,z.name as zone_name
        from sensor s,device d,regionlevel g,regionlevel c,regionlevel f,regionlevel z
        where  d.id=s.device_id and g.id=s.group_id and c.id=s.company_id and f.id = s.factory_id and z.id=s.zone_id 
        LIMIT {max(0, (page - 1) * size)},{size}"""
        sql_1 = f"select count(id) as num from sensor"

    total_num = db_connect(sql_1)

    total = total_num["data"][0]["num"]
    res = db_connect(sql)
    if res["code"] == 0:
        return {
            "msg": "查询成功",
            "code": 0,
            "data": res["data"],
            "pagination": {"page": page, "pageSize": size, "total": total},
        }
    else:
        return {"msg": "查询失败", "code": -1, "data": []}


def get_conn(db_name=None, dbconfig=None):
    d = current_app.config["settings"]["mysql"]
    db_name = d["dbname"] if db_name is None else db_name
    dbconfig = d if dbconfig is None else dbconfig

    return pymysql.connect(
        host=dbconfig["host"],
        user=dbconfig["user"],
        port=dbconfig["port"],
        password=dbconfig["password"],
        db=db_name,
        charset="utf8",
        connect_timeout=10,
        read_timeout=10,
        write_timeout=10,
    )


@sensor.route("/getlist", methods=["POST"])
def get_sensor_list1():
    a = request.json
    device_name = assign_value(a, "device_name")
    status = assign_value(a, "status")
    sensor_type = assign_value(a, "sensor_type")
    sensor_name = assign_value(a, "sensor_name")
    address = assign_value(a, "address")
    port = assign_value(a, "port")
    id = assign_value(a, "id")
    scope_id = assign_value(a, "scope_id", 0)
    scoped = parsing_data_id(scope_id, request)
    # 基础语句
    sql = """SELECT s.id,s.sensor_name,s.device_id,s.group_id,s.company_id,s.factory_id,s.zone_id,s.address,s.port,s.sensor_type,s.eigenvalue,s.offset,s.create_time,s.direction,s.fs,s.status,d.device_name,r.proj_no from sensor s LEFT JOIN device d on d.id = s.device_id LEFT JOIN regionlevel r on r.id = s.group_id"""
    # 条件语句
    query = " where 1=1 "
    if scoped != "":
        query += f" and d.{scoped}"
    if id != "":
        query += f" and s.id = {id} "
    if sensor_name != "":
        query += f" and s.sensor_name like '%{sensor_name}%'"
    if status != "":
        query += f" and s.status like '%{status}%'"
    if sensor_type != "":
        query += f" and s.sensor_type = {sensor_type}"
    if device_name != "":
        query += f" and d.device_name like '%{device_name}%'"
    if address != "":
        query += f" and s.address like '%{address}%'"
    if port != "":
        query += f" and s.port like '%{port}%'"

    sql_1 = f"select count(id) as num from ({sql} {query} and s.status = '在线') as a"
    sql_2 = f"select count(id) as num from ({sql} {query} and s.status = '离线') as a"
    sql = sql + query + " ORDER BY s.create_time desc"
    res = db_pagelist(sql, request.json["page"], request.json["size"])
    num1 = db_fetchone(sql_1)
    num2 = db_fetchone(sql_2)
    res["stat"] = {
        "on": num1["data"],
        "un": num2["data"],
    }
    
    now_time = datetime.now()
    for t in res['data']:
        try:
            db = current_app.mgdb[f"ieplat_{t['proj_no']}"]
            mg_addr = t['address'] if '_' in t['address'] else f"{t['address']}_0"
            id_stime = int((now_time - timedelta(days=360)).timestamp())
            id_etime = int(now_time.timestamp())
            query_eige = {"$and": [{"_id": {"$gte": f"{t['port']}_{mg_addr}_{id_stime}"}},
                                    {"_id": {"$lte": f"{t['port']}_{mg_addr}_{id_etime}"}}, ]}
            query_wave = {"$and": [{"_id": {"$gte": f"{t['port']}_{mg_addr}_Z_{id_stime}"}},
                                    {"_id": {"$lte": f"{t['port']}_{mg_addr}_Z_{id_etime}"}}, ]}
            dbname = get_collection_name(db,f"eige_{t['port']}_{t['address'].split('_')[0]}")
            eiges = list(db[dbname].find(query_eige, {"_id": 1, "data.Z.vel_rms": 1, "data.tem": 1, "data.rssi": 1, "data.voltage": 1, "data.a_cur": 1})
                            .sort('_id', -1).limit(1))
            waves = list(db['wave'].find(
                query_wave, {"_id": 1}).sort('_id', -1).limit(1))
            t['time'] = []
            t['data'] = []
            for eige in eiges:
                tt = datetime.fromtimestamp(int(eige['_id'][-10:])).strftime('%Y-%m-%d %H:%M:%S')
                t['time'].append({
                    'text': '特征值',
                    'value': tt
                })
                md = eige['data'].get('Z', None)
                tem = eige['data'].get('tem', None)
                if tem is not None:
                    t['data'].append({
                        'text': '温度',
                        'value': str(tem)
                    })
                rpm = eige['data'].get('rpm', None)
                if rpm is not None:
                    t['data'].append({
                        'text': '转速',
                        'value': str(rpm)
                    })
                rssi = eige['data'].get('rssi', None)
                if rssi is not None:
                    t['data'].append({
                        'text': '信号强度',
                        'value': str(rssi)
                    })
                voltage = eige['data'].get('voltage', None)
                if voltage is not None:
                    t['data'].append({
                        'text': '电压',
                        'value': str(voltage)
                    })
                a_cur = eige['data'].get('a_cur', None)
                if a_cur is not None:
                    t['data'].append({
                        'text': '电流',
                        'value': str(a_cur)
                    })
                rms = md.get('vel_rms', None) if md is not None else None
                if rms is not None:
                    t['data'].append({
                        'text': '振动速度',
                        'value': str(rms)
                    })
            for w in waves:
                t['time'].append({
                    'text': '波形',
                    'value': datetime.fromtimestamp(int(w['_id'][-10:])).strftime('%Y-%m-%d %H:%M:%S')
                })
        except Exception as e:
            traceback.print_exc()
    return res


@sensor.route("/gear", methods=["get"])
def gear():
    sql = """SELECT  gear_name, gear_num FROM sensor WHERE gear_num IS NOT NULL"""
    res = db_pagelist(sql, 1, 999)
    return res


# 添加传感器
@sensor.route("/add", methods=["post"])
def sensor_add():
    res = db_insert("sensor", request.json)
    utilsapp.excel_util.create_table(
        [request.json], current_app.config["settings"]["mysql"]
    )
    return res


# 传感器类型——删除
@sensor.route("/delete", methods=["POST"])
def sensor_delete():
    id = request.json["id"]
    res = db_delete("sensor", f"WHERE id=%s", {"id": id})
    db_delete("warn", f"WHERE sensor_id=%s", {"sensor_id": id})
    # utilsapp.excel_util.drop_table(
    #     [request.json], current_app.config["settings"]["mysql"]
    # )
    return res

@sensor.route('deletes', methods=['post'])
def deletes():
    id_ = ' or '.join([('id = ' + str(item)) for item in request.json['idList']])
    sql = f"DELETE FROM sensor where {id_}"
    return db_deletes(sql)

@sensor.route("/download",methods=['post'])
def download():
    date = time.strftime("%Y%m%d", time.localtime())
    filename = request.json['filename']
    scope_id = assign_value(request.json, "scope_id", 0)
    scoped = parsing_data_id(scope_id, request)
    # 基础语句
    sql = f"""SELECT s.*,d.device_name,r.proj_no from sensor s LEFT JOIN device d on d.id = s.device_id LEFT JOIN regionlevel r on r.id = s.group_id where s.{scoped}"""
    print(sql)
    data = db_list(sql)['data']
    now_time = datetime.now()
    for t in data:
        try:
            db = current_app.mgdb[f"ieplat_{t['proj_no']}"]
            mg_addr = t['address'] if '_' in t['address'] else f"{t['address']}_0"
            id_stime = int((now_time - timedelta(days=360)).timestamp())
            id_etime = int(now_time.timestamp())
            query_eige = {"$and": [{"_id": {"$gte": f"{t['port']}_{mg_addr}_{id_stime}"}},
                                    {"_id": {"$lte": f"{t['port']}_{mg_addr}_{id_etime}"}}, ]}
            query_wave = {"$and": [{"_id": {"$gte": f"{t['port']}_{mg_addr}_Z_{id_stime}"}},
                                    {"_id": {"$lte": f"{t['port']}_{mg_addr}_Z_{id_etime}"}}, ]}
            eiges = list(db['eige'].find(query_eige, {"_id": 1, "data.Z.vel_rms": 1, "data.tem": 1, "data.rssi": 1, "data.voltage": 1, "data.a_cur": 1})
                            .sort('_id', -1).limit(1))
            waves = list(db['wave'].find(
                query_wave, {"_id": 1}).sort('_id', -1).limit(1))
            t['time'] = []
            t['data'] = []
            for eige in eiges:
                tt = datetime.fromtimestamp(int(eige['_id'][-10:])).strftime('%Y-%m-%d %H:%M:%S')
                t['time'].append({
                    'text': '特征值',
                    'value': tt
                })
                md = eige['data'].get('Z', None)
                tem = eige['data'].get('tem', None)
                if tem is not None:
                    t['data'].append({
                        'text': '温度',
                        'value': str(tem)
                    })
                rpm = eige['data'].get('rpm', None)
                if rpm is not None:
                    t['data'].append({
                        'text': '转速',
                        'value': str(rpm)
                    })
                rssi = eige['data'].get('rssi', None)
                if rssi is not None:
                    t['data'].append({
                        'text': '信号强度',
                        'value': str(rssi)
                    })
                voltage = eige['data'].get('voltage', None)
                if voltage is not None:
                    t['data'].append({
                        'text': '电压',
                        'value': str(voltage)
                    })
                a_cur = eige['data'].get('a_cur', None)
                if a_cur is not None:
                    t['data'].append({
                        'text': '电流',
                        'value': str(a_cur)
                    })
                rms = md.get('vel_rms', None) if md is not None else None
                if rms is not None:
                    t['data'].append({
                        'text': '振动速度',
                        'value': str(rms)
                    })
            for w in waves:
                t['time'].append({
                    'text': '波形',
                    'value': datetime.fromtimestamp(int(w['_id'][-10:])).strftime('%Y-%m-%d %H:%M:%S')
                })
        except Exception as e:
            traceback.print_exc()
    res_ = []
    for i in data:
        its1 = ['设备名称','测点名称','地址','端口','测点状态']
        its2 = ['device_name','sensor_name','address','port','status']
        d = {}
        for index in range(len(its1)):
            d[its1[index]] = i[its2[index]] 
        for item in i['time']:
            d[item['text']] = item['value']
        for item in i['data']:
            d[item['text']] = item['value']
        res_.append(d)
    df = pandas.DataFrame(res_)
    script_dir = os.path.dirname(os.path.abspath(__name__))
    filePath = script_dir + '/static/upload/'+date
    if not os.path.exists(filePath):
        os.mkdir(filePath)
    filePath+=f"/{filename}"
    df.to_excel(filePath, index=False)
    workbook = load_workbook(filePath)
    worksheet = workbook.active
    for column in worksheet.columns:
        worksheet.column_dimensions[column[0].column_letter].width = 20
    workbook.save(filePath)
    return send_file(filePath, as_attachment=True, attachment_filename=filename)


@sensor.route("/edit", methods=["POST"])
def sensor_edit():
    id = request.json["id"]
    keys_not_add = ["id", "data", "time", "device_name","proj_no"]
    addModel = {
        key: request.json[key] for key in request.json if key not in keys_not_add
    }
    res = db_update("sensor", addModel, f"WHERE id=%s", {"id": id})
    utilsapp.excel_util.create_table(
        [request.json], current_app.config["settings"]["mysql"]
    )
    return res


# 查询传感器详情
@sensor.route("/details")
def get_device_detail():
    sensor_id = int(request.args.get("id"))
    sql = f"""SELECT s.id,g.proj_no,t.show as wave_show,t.topic,s.fs,t.sys_eig,t.sys_wave,s.sensor_name,s.status,s.direction,s.address,s.port,t.eigenvalue,s.sensor_type,t.name as 
    sensor_type_name,t.characters,accelerate,g.name as group_name ,t.single,c.name as company_name,f.name as factory_name,
    z.name as zone_name,d.device_name,dt.`name` as device_type,d.id as device_id
		from sensor s
		LEFT JOIN sensor_type t on t.id =s.sensor_type 
		LEFT JOIN device d on d.id = s.device_id
		LEFT JOIN device_type dt on d.type_id = dt.id
		LEFT JOIN regionlevel z on z.id = s.zone_id
		LEFT JOIN regionlevel f on f.id = s.factory_id
		LEFT JOIN regionlevel c on c.id = s.company_id
		LEFT JOIN regionlevel g on g.id = s.group_id 
		where s.id = {sensor_id}"""
    res = db_connect(sql)
    if res["data"]:
        item = res["data"][0]
        ss1 = f"select * from warn where is_show=1 and notice in (0,1) and sensor_id={item.get('id')} order by id desc"
        warn = db_list(ss1)["data"]
        item["warn"] = warn
        pass
    if res["code"] == 0 and res["data"]:
        return {
            "msg": "查询成功",
            "code": 0,
            "data": res["data"][0],
        }
    else:
        return {"msg": "查询失败", "code": -1, "data": []}


# 查询传感器树形结构
@sensor.route("/tree/getlist")
def get_sensor_tree():
    try:
        zone_id = int(request.args.get("id"))
        type = assign_value(request.args, "type", "sensor")

        sql = f"""SELECT s.id,s.sensor_name ,s.address,s.direction,d.device_name,d.id as device_id, r.name,s.zone_id,st.sys_eig,st.single
FROM regionlevel r 
LEFT JOIN device d on d.zone_id=r.id 
LEFT JOIN sensor s on d.id=s.device_id
LEFT JOIN sensor_type st on st.id = s.sensor_type
where r.id={zone_id} ORDER BY s.id ASC"""
        res = db_connect(sql)
        zone_dict = dict()
        device_dict = dict()
        for i in res["data"]:
            if i["device_name"] not in device_dict.keys():
                device_dict[i["device_name"]] = i["device_id"]
            if i["name"] not in zone_dict.keys():
                zone_dict[i["name"]] = i["zone_id"]
        ret = list()
        for z in zone_dict.keys():
            ztemp = dict()
            ztemp["label"] = z
            ztemp["value"] = zone_dict[z]
            ztemp["children"] = list()

            for d in device_dict.keys():
                dtemp = dict()
                dtemp["label"] = d
                dtemp["value"] = device_dict[d]
                dtemp["children"] = list()
                if type == "sensor":
                    for i in res["data"]:
                        if i["name"] == z and i["device_name"] == d:
                            stemp = dict()
                            stemp["label"] = i["sensor_name"]
                            stemp["value"] = i["id"]
                            stemp["direction"] = i["direction"]
                            stemp["sys_eig"] = i["sys_eig"]
                            stemp["single"] = i["single"]
                            stemp["address"] = i["address"]
                            dtemp["children"].append(stemp)
                ztemp["children"].append(dtemp)
            ret.append(ztemp)
            if type == "sensor":
                for item in ret[0].get("children"):
                    device_level = []
                    for item1 in item.get("children"):
                        ss = f"select max(warn_level) as s_level  from warn where is_show=1 and sensor_id={item1.get('value')} and notice=0"
                        ssres = db_fetchone(ss)
                        sensor_level = (
                            0 if len(str(ssres["data"])) <= 0 else ssres["data"]
                        )
                        item1["level"] = sensor_level
                        device_level.append(sensor_level)
                    item["level"] = max(device_level)

        return {
            "msg": "查询成功",
            "code": 0,
            "data": ret,
        }
    except:
        return {"msg": "查询失败", "code": -1, "data": []}


# 根据设备ID查询测点下特征值的最新数据
@sensor.route("/getSensorEigensByDevice", methods=["post", "GET"])
def get_sensor_eigen():
    did = request.json["id"]

    dbres_s = db_list(
        f"""SELECT sensor.id,sensor_name,address,`port`,sensor_type.eigenvalue,graph FROM sensor
LEFT JOIN sensor_type on sensor_type.id=sensor.sensor_type where device_id=%s""",
        {"did": did},
    )

    if dbres_s["code"] != 0 or len(dbres_s["data"]) <= 0:
        return utils.fail("设备暂无测点数据")
    now = datetime.now()

    dbres_e = db_table_list("eigenvalue")
    if dbres_e["code"] != 0:
        return dbres_e
    eigs = dbres_e["data"]

    for s in dbres_s["data"]:
        if len(s["eigenvalue"]) <= 0:
            continue
        eig_ids = s["eigenvalue"].split(",")
        s_eigs = [e for e in eigs if str(e["id"]) in eig_ids]
        if len(s_eigs) <= 0:
            continue

        # 如果当月没有数据往前推

        dbname = get_sensor_new_table(s["id"])
        d_table = (
            dbname
            if dbname is not None and len(dbname) > 0
            else f'd_{s["port"]}_{s["address"]}_{now.year}{now.month:02}'
        )

        # for i in range(3):
        #     d_table = f'd_{s["port"]}_{s["address"]}_{now.year}{now.month - i:02}'
        #     dbres_c = db_fetchone(f'SELECT COUNT(1) FROM {d_table}', conn)
        #     if dbres_c['code'] == 0 and int(dbres_c['data']) > 0:
        #         break

        sqls = []
        for e in s_eigs:
            sql = f"""(SELECT feature_key,`value`,'{e["name"]}' as nickname,'{e["unit"]}' as unit 
                    FROM {d_table} where feature_key ='{e["nickname"]}' ORDER BY id desc LIMIT 0,1)"""
            sqls.append(sql)
        sql = " UNION ALL ".join(sqls)
        dbres_d = db_list(sql, _conn="conn")

        s["eigs"] = [] if dbres_d["code"] != 0 else dbres_d["data"]

    r = dbres_s["data"]
    return utils.ok(r)


@sensor.route("/upload", methods=["POST"])
def upload():
    try:
        # 检查请求中是否包含文件
        if "file" not in request.files:
            return utils.fail("空文件")
        file = request.files["file"]
        # 检查文件是否为空
        if file.filename == "":
            return utils.fail("空文件")
        
        date = time.strftime("%Y%m%d", time.localtime())
        ext = os.path.splitext(file.filename)[-1]

        script_dir = os.path.dirname(os.path.abspath(__name__)) + f"/static/{date}"

        if "xls" not in ext:
            return utils.fail(None, "导入失败,文件格式不正确")

        Path(script_dir).mkdir(exist_ok=True,parents=True)
        
        filePath = script_dir + f"/{uuid.uuid1()}{ext}"
        # 保存文件到指定目录
        file.save(filePath)
        dbconfig = current_app.config["settings"]["mysql"]
        return utilsapp.excel_util.main(dbconfig, filePath, True)
    except Exception as e:
        error_message = traceback.format_exc()
        current_app.add_log("测点导入", error_message)
        return utils.fail(None, "导入失败", e)


@sensor.route("/set/terminal", methods=["post"])
def assdsa():
    try:
        number = assign_value(request.json, "number")
        zone_id = assign_value(request.json, "id")
        parts = number.split("_")
        port = parts[0]
        address = parts[1]
        sql = f"""
        SELECT address,port,id,sensor_name from sensor 
    where zone_id = {zone_id} AND port = '{port}' and address like '{address}%' 
        ORDER BY address 
        """
        return db_list(sql)
    except Exception as e:
        return utils.fail(None, "终端编号格式不正确", e)





